package cn.idongjia.task.client.mapper;

import cn.idongjia.analyze.pojo.*;
import cn.idongjia.analyze.query.SellsQuery;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * Created by leedongwei on 16/1/5.
 * 数据分析相关
 */
@Component("dataAnalysisMapper")
public interface DataAnalysisMapper {

    @Select("SELECT i.uid,u.username,i.iid,i.createtm ,i.category  " +
            "FROM kp_item i,kp_user u WHERE  i.createtm>=#{startTm} AND i.createtm<#{endTm}  and i.status =1 and i.uid = u.uid")
    List<OriginalItem> findOriginalItems(@Param("startTm") Long startTm, @Param("endTm") Long endTm);

    @Select("SELECT o.ooid,o.uid,o.createtm,o.paytm,o.sendtm,o.receivetm FROM kp_item_order o WHERE o.createtm>=#{startTm} AND o.createtm<#{endTm}  and o.status=2 or o.status=3 or o.status=4 or o.status=5 or o.status=6 or o.status=7")
    List<OriginalOrder> findOriginalOrders(@Param("startTm") Long startTm, @Param("endTm") Long endTm);


//    @Insert("<script>" +
//            "replace into kp_analysis_item_detail (uid,category,time,ltime,username,publishNum,sellNum,orderNum,price,perPrice)   " +
//            "    values  \n" +
//            "    <foreach collection=\"list\" item=\"item\" index=\"index\" separator=\",\" >  \n" +
//            "        (#{item.uid},#{item.category},#{item.time},#{item.ltime},#{item.username},#{item.publishNum},#{item.sellNum},#{item.orderNum},#{item.price},#{item.perPrice}) " +
//            "    </foreach> " +
//            "</script>")
//    int batchInsertResultItemDetail(@Param("list") List<ResultItemDetail> itemDetailAnalysises);

    @Select("SELECT o.ooid,o.uid,u.username,o.realpay price,o.iid,i.category,o.quantity num FROM" +
            " kp_item i, kp_item_order o,kp_user u WHERE i.iid= o.iid AND o.uid = u.uid and o.ooid=#{ooid}")
    List<OriginalOrderSub> findOriginalOrderSubs(@Param("ooid") String ooid);

    @Select("<script>" +
            "SELECT * FROM kp_analysis_item_detail " +
            "<where> " +
            "1=1" +
            "       <if test=\"resultuid != null\">" +
            "       AND uid = #{resultuid}        " +
            "       </if>" +
            "       <if test=\"resulttime != null\">" +
            "       AND time = #{resulttime}        " +
            "       </if>" +
            "       <if test=\"resultusername != null\">" +
            "       AND username = #{resultusername}        " +
            "       </if>" +
            "       <if test=\"resultcategory != null\">" +
            "       AND category = #{resultcategory}        " +
            "       </if>" +
            "       <if test=\"resultstartTm != null\">" +
            "       AND ltime   &gt; #{resultstartTm}        " +
            "       </if>" +
            "       <if test=\"resultendTm != null\">" +
            "       AND ltime   &lt;= #{resultendTm}        " +
            "       </if>" +

            "</where> " +
            "</script>")
    List<ResultItemDetail> findResultItemDetail(@Param("resultQuery") ResultQuery resultQuery);

//    @Insert("<script>" +
//            "replace into kp_analysis_item_list (uid,category,time,username," +
//            "pubNumY,sellNumY,orderNumY,priceY,perPriceY,pubNum7,sellNum7,orderNum7,price7,perPrice7," +
//            "pubNum30,sellNum30,orderNum30,price30,perPrice30,pubNum,sellNum,orderNum,price,perPrice)   " +
//            "    values  \n" +
//            "    <foreach collection=\"list\" item=\"item\" index=\"index\" separator=\",\" >  \n" +
//            "        (#{item.uid},#{item.category},#{item.time},#{item.username}," +
//            "         #{item.pubNumY},#{item.sellNumY},#{item.orderNumY},#{item.priceY},#{item.perPriceY},#{item.pubNum7}," +
//            "         #{item.sellNum7},#{item.orderNum7},#{item.price7},#{item.perPrice7},#{item.pubNum30},#{item.sellNum30},#{item.orderNum30}," +
//            "         #{item.price30},#{item.perPrice30},#{item.pubNum},#{item.sellNum},#{item.orderNum},#{item.price},#{item.perPrice}) " +
//            "    </foreach> " +
//            "</script>")
//    int batchInsertitemListAnalysises(@Param("list") List<ResultItemList> itemListAnalysises);

    @Select("<script>" +
            "SELECT * FROM kp_analysis_item_list WHERE " +
            "1=1" +
            "       <if test=\"resultQuery.uid != null\">" +
            "       AND uid = #{resultQuery.uid}        " +
            "       </if>" +
            "       <if test=\"resultQuery.time != null\">" +
            "       AND time = #{resultQuery.time}        " +
            "       </if>" +
            "       <if test=\"resultQuery.username != null\">" +
            "       AND username = #{resultQuery.username}        " +
            "       </if>" +
            "       <if test=\"resultQuery.category != null\">" +
            "       AND category = #{resultQuery.category}        " +
            "       </if>" +
            "</script>")
    List<ResultItemList> findResultItemList(@Param("resultQuery") ResultQuery resultQuery);


//        @Insert("<script>" +
//                "replace into kp_analysis_item_list (uid,category,time,username," +
//                "pubNumY,sellNumY,orderNumY,priceY,perPriceY)" +
//                "    values  \n" +
//                "    <foreach collection=\"list\" item=\"item\" index=\"index\" separator=\",\" >  \n" +
//                "        (#{item.uid},#{item.category},#{item.time},#{item.username}," +
//                "         #{item.pubNumY},#{item.sellNumY},#{item.orderNumY},#{item.priceY},#{item.perPriceY})" +
//                "    </foreach> " +
//                "</script>")
//        void batchInsertitemListAnalysises1(@Param("list")List<ResultItemList> itemListAnalysises);

    @Insert("INSERT INTO kp_analysis_item_list (uid,category,time,username,pubNumY,sellNumY,orderNumY,priceY,perPriceY) " +
            "VALUES " +
            "(#{uid},#{category},#{time},#{username},#{pubNumY},#{sellNumY},#{orderNumY},#{priceY},#{perPriceY}) " +
            "ON DUPLICATE KEY UPDATE pubNumY=#{pubNumY},sellNumY=#{sellNumY},orderNumY=#{orderNumY},priceY=#{priceY},perPriceY=#{perPriceY}"
    )
    void insertItemListAnalysises1(ResultItemList itemListAnalys);

    //        @Insert("<script>" +
//                "replace into kp_analysis_item_list (uid,category,time,username," +
//                "pubNum7,sellNum7,orderNum7,price7,perPrice7)" +
//                "    values  \n" +
//                "    <foreach collection=\"list\" item=\"item\" index=\"index\" separator=\",\" >  \n" +
//                "        (#{item.uid},#{item.category},#{item.time},#{item.username}," +
//                "         #{item.pubNum7},#{item.sellNum7}," +
//                "         #{item.orderNum7},#{item.price7},#{item.perPrice7})" +
//                "    </foreach> " +
//                "</script>")
//        void batchInsertitemListAnalysises2(@Param("list")List<ResultItemList> itemListAnalysises);
    @Insert("INSERT INTO kp_analysis_item_list (uid,category,time,username,pubNum7,sellNum7,orderNum7,price7,perPrice7) " +
            "VALUES " +
            "(#{uid},#{category},#{time},#{username},#{pubNum7},#{sellNum7},#{orderNum7},#{price7},#{perPrice7}) " +
            "ON DUPLICATE KEY UPDATE pubNum7=#{pubNum7},sellNum7=#{sellNum7},orderNum7=#{orderNum7},price7=#{price7},perPrice7=#{perPrice7}")
    void insertItemListAnalysises2(ResultItemList itemListAnalysis);
//        @Insert("<script>" +
//                "replace into kp_analysis_item_list (uid,category,time,username," +
//                "pubNum30,sellNum30,orderNum30,price30,perPrice30)   " +
//                "    values  \n" +
//                "    <foreach collection=\"list\" item=\"item\" index=\"index\" separator=\",\" >  \n" +
//                "        (#{item.uid},#{item.category},#{item.time},#{item.username}," +
//                "         #{item.pubNum30},#{item.sellNum30},#{item.orderNum30}," +
//                "         #{item.price30},#{item.perPrice30}) " +
//                "    </foreach> " +
//                "</script>")
//        void batchInsertitemListAnalysises3(@Param("list")List<ResultItemList> itemListAnalysises);

    @Insert("INSERT INTO kp_analysis_item_list (uid,category,time,username,pubNum30,sellNum30,orderNum30,price30,perPrice30) " +
            "VALUES " +
            "(#{uid},#{category},#{time},#{username},#{pubNum30},#{sellNum30},#{orderNum30},#{price30},#{perPrice30}) " +
            "ON DUPLICATE KEY UPDATE pubNum30=#{pubNum30},sellNum30=#{sellNum30},orderNum30=#{orderNum30},price30=#{price30},perPrice30=#{perPrice30}")
    void insertItemListAnalysises3(ResultItemList itemListAnalysis);

    //        @Insert("<script>" +
//                "replace into kp_analysis_item_list (uid,category,time,username," +
//                "pubNum,sellNum,orderNum,price,perPrice)   " +
//                "    values  \n" +
//                "    <foreach collection=\"list\" item=\"item\" index=\"index\" separator=\",\" >  \n" +
//                "        (#{item.uid},#{item.category},#{item.time},#{item.username}," +
//                "         #{item.pubNum},#{item.sellNum},#{item.orderNum},#{item.price},#{item.perPrice}) " +
//                "    </foreach> " +
//                "</script>")
//        void batchInsertitemListAnalysises4(@Param("list")List<ResultItemList> itemListAnalysises);
    @Insert("INSERT INTO kp_analysis_item_list (uid,category,time,username,pubNum,sellNum,orderNum,price,perPrice) " +
            "VALUES " +
            "(#{uid},#{category},#{time},#{username},#{pubNum},#{sellNum},#{orderNum},#{price},#{perPrice}) " +
            "ON DUPLICATE KEY UPDATE pubNum=#{pubNum},sellNum=#{sellNum},orderNum=#{orderNum},price=#{price},perPrice=#{perPrice}")
    void insertItemListAnalysises4(ResultItemList itemListAnalysises);
//        @Insert("<script>" +
//                "replace into kp_analysis_item_detail (uid,category,time,ltime,username,publishNum)   " +
//                "    values  \n" +
//                "    <foreach collection=\"list\" item=\"item\" index=\"index\" separator=\",\" >  \n" +
//                "        (#{item.uid},#{item.category},#{item.time},#{item.ltime},#{item.username},#{item.publishNum}) " +
//                "    </foreach> " +
//                "</script>")
//        void batchInsertResultItemDetail1(@Param("list")List<ResultItemDetail> itemDetailAnalysises);
//        @Insert("<script>" +
//                "replace into kp_analysis_item_detail (uid,category,time,ltime,username,sellNum,orderNum,price,perPrice)   " +
//                "    values  \n" +
//                "    <foreach collection=\"list\" item=\"item\" index=\"index\" separator=\",\" >  \n" +
//                "        (#{item.uid},#{item.category},#{item.time},#{item.ltime},#{item.username},#{item.sellNum},#{item.orderNum},#{item.price},#{item.perPrice}) " +
//                "    </foreach> " +
//                "</script>")
//        void batchInsertResultItemDetail2(@Param("list")List<ResultItemDetail> itemDetailAnalysises);

    //单个处理,批量暂时没有找到用方案,使用replace into 会导致清除原数据,重新写入问题

    @Insert("INSERT INTO kp_analysis_item_detail (uid,category,time,ltime,username,publishNum) " +
            "VALUES " +
            "(#{uid},#{category},#{time},#{ltime},#{username},#{publishNum} )" +
            "ON DUPLICATE KEY UPDATE publishNum=#{publishNum} ")
    void insertResultItemDetail1(ResultItemDetail itemDetailAnalysis);

    @Insert("INSERT INTO kp_analysis_item_detail (uid,category,time,ltime,username,sellNum,orderNum,price,perPrice) " +
            "VALUES " +
            " (#{uid},#{category},#{time},#{ltime},#{username},#{sellNum},#{orderNum},#{price},#{perPrice})  " +
            "ON DUPLICATE KEY UPDATE sellNum=#{sellNum},orderNum= #{orderNum},price=#{price},perPrice=#{perPrice}")
    void insertResultItemDetail2(ResultItemDetail itemDetailAnalysis);

    @Select("<script>" +
            "select c.`name` category,s.*,c.icid cid" +
            " from kp_analysis_item_list s" +
            " left join kp_item_category c ON c.icid=s.category" +
            " <where>" +
            " 1=1" +
            " <if test=\"uid != null\"> AND s.uid = #{uid} </if>" +
            " <if test=\"cid != null\"> AND c.icid = #{cid} </if>" +
            " <if test=\"username != null\"> AND s.username like #{username} </if>" +
            " <if test=\"category != null\"> AND c.name like #{category} </if>" +
            " </where>" +
            " <if test=\"orderBy != null\">" +
            " ORDER BY ${orderBy} " +
            " </if>" +
            " <if test=\"limit != null\">" +
            " LIMIT ${limit} " +
            " </if>" +
            " <if test=\"offset != null\">" +
            " OFFSET ${offset} " +
            " </if>" +
            "</script>")
    List<SellsStatistics> getSellsStatistics(SellsQuery query);

    @Select("<script>" +
            "select count(*)" +
            " from kp_analysis_item_list s" +
            " left join kp_item_category c ON c.icid=s.category" +
            " <where>" +
            " 1=1" +
            " <if test=\"uid != null\"> AND s.uid = #{uid} </if>" +
            " <if test=\"cid != null\"> AND c.icid = #{cid} </if>" +
            " <if test=\"username != null\"> AND s.username like #{username} </if>" +
            " <if test=\"category != null\"> AND c.name like #{category} </if>" +
            " </where>" +
            "</script>")
    int countSellsStatistics(SellsQuery query);
}
